package com.zzzzzz.content.topic;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.zzzzzz.core.dao.BaseDao;

@Repository
public class TopicDao extends BaseDao {
	
	/*private static final String ADD = 										"INSERT INTO topic(title,text_s,text_n,review,pic_id,tags,user_id,add_dt,upd_dt,status,type_id,looks,replies,likes) VALUES(:title,:textS,:textN,:review,:picId,:tags,:userId,:addDt,:updDt,:status,:typeId,:looks,:replies,:likes)";
	private static final String EDIT = 										"update topic set title=:title, text_s=:textS, text_n=:textN, review=:review, pic_id=:picId, tags=:tags, upd_dt=:updDt where id=:id and user_id=:userId";
	private static final String EDIT_STATUS = 								"update topic set status = ?, upd_dt = ? where id = ? and user_id = ?";
	private static final String EDIT_PIC = 									"update topic set pic_id = ?, upd_dt = ? where id = ? and user_id = ?";
	
	private final static String EDIT_LOOKS = 								"update topic set looks = looks + ? where id = ?";
	private static final String EDIT_REPLIES = 								"update topic set replies = replies + ? where id = ?";
	private final static String EDIT_LIKES = 								"update topic set likes = likes + ? where id = ?";
	
	private static final String FIND_TOPIC_BY_ID = 							"select t.id, t.title, t.text_s, t.text_n, t.review, t.pic_id, t.tags, t.user_id, t.add_dt, t.upd_dt, t.status, t.type_id, t.looks, t.replies, t.likes from topic t where t.id = ?";
	private static final String FIND_TOPICVIEW_BY_ID = 						"select t.id, t.title, t.text_s, t.text_n, t.review, t.pic_id, t.tags, t.user_id, t.add_dt, t.upd_dt, t.status, t.type_id, t.looks, t.replies, t.likes, u.username, (case when u.website is not null then u.website else u.id end) as website from topic t inner join user u on t.user_id = u.id where t.id = ?";
	
	private static final String FIND_TOPICLISTVIEW = 						"select t.id, t.title, t.text_s, t.review, t.pic_id, t.tags, t.user_id, t.add_dt, t.upd_dt, t.status, t.type_id, t.looks, t.replies, t.likes, u.username, (case when u.website is not null then u.website else u.id end) as website from topic t inner join user u on t.user_id = u.id where t.status = 0";
	private static final String FIND_TOPICLISTVIEW_COUNT = 	 				"select count(1) as count from topic t inner join user u on t.user_id = u.id where t.status = 0";
	
	private static final String FIND_TOPICLISTVIEW_BY_TAGID = 				"select t.id, t.title, t.text_s, t.review, t.pic_id, t.tags, t.user_id, t.add_dt, t.upd_dt, t.status, t.type_id, t.looks, t.replies, t.likes, u.username, (case when u.website is not null then u.website else u.id end) as website from topic t inner join topic_tag tt on t.id = tt.topic_id inner join user u on t.user_id = u.id where tt.tag_id = ? and t.status = 0";
	private static final String FIND_TOPICLISTVIEW_BY_TAGID_COUNT = 	 	"select count(1) as count from topic t inner join topic_tag tt on t.id = tt.topic_id inner join user u on t.user_id = u.id where tt.tag_id = ? and t.status = 0";

	protected class TopicRowMapper implements RowMapper<Topic> {
		public Topic mapRow(ResultSet rs, int rn) throws SQLException {
			Topic topic = new Topic();
			topic.setId(rs.getLong("id"));
			topic.setTitle(rs.getString("title"));
			topic.setTextS(rs.getString("text_s"));
			topic.setTextN(rs.getString("text_n"));
			topic.setReview(rs.getString("review"));
			topic.setPicId(rs.getString("pic_id"));
			topic.setUserId(rs.getLong("user_id"));
			topic.setTags(rs.getString("tags"));
			topic.setAddDt(rs.getTimestamp("add_dt"));
			topic.setUpdDt(rs.getTimestamp("upd_dt"));
			topic.setStatus(rs.getInt("status"));
			topic.setTypeId(rs.getInt("type_id"));
			topic.setLooks(rs.getInt("looks"));
			topic.setLikes(rs.getInt("likes"));
			topic.setReplies(rs.getInt("replies"));
			return topic;
		}
	}
	
	protected class TopicViewRowMapper implements RowMapper<TopicView> {
		public TopicView mapRow(ResultSet rs, int rn) throws SQLException {
			TopicView topicView = new TopicView();
			topicView.setId(rs.getLong("id"));
			topicView.setTitle(rs.getString("title"));
			topicView.setTextS(rs.getString("text_s"));
			topicView.setTextN(rs.getString("text_n"));
			topicView.setReview(rs.getString("review"));
			topicView.setPicId(rs.getString("pic_id"));
			topicView.setUserId(rs.getLong("user_id"));
			topicView.setTags(rs.getString("tags"));
			topicView.setAddDt(rs.getTimestamp("add_dt"));
			topicView.setUpdDt(rs.getTimestamp("upd_dt"));
			topicView.setTypeId(rs.getInt("type_id"));
			topicView.setLooks(rs.getInt("looks"));
			topicView.setLikes(rs.getInt("likes"));
			topicView.setReplies(rs.getInt("replies"));
			topicView.setUsername(rs.getString("username"));
			topicView.setWebsite(rs.getString("website"));
			return topicView;
		}
	}
	
	protected class TopicListViewRowMapper implements RowMapper<TopicListView> {
		public TopicListView mapRow(ResultSet rs, int rn) throws SQLException {
			TopicListView topicListView = new TopicListView();
			topicListView.setId(rs.getLong("id"));
			topicListView.setTitle(rs.getString("title"));
			topicListView.setTextS(rs.getString("text_s"));
			topicListView.setReview(rs.getString("review"));
			topicListView.setPicId(rs.getString("pic_id"));
			topicListView.setUserId(rs.getLong("user_id"));
			topicListView.setTags(rs.getString("tags"));
			topicListView.setAddDt(rs.getTimestamp("add_dt"));
			topicListView.setUpdDt(rs.getTimestamp("upd_dt"));
			topicListView.setTypeId(rs.getInt("type_id"));
			topicListView.setLooks(rs.getInt("looks"));
			topicListView.setLikes(rs.getInt("likes"));
			topicListView.setReplies(rs.getInt("replies"));
			topicListView.setUsername(rs.getString("username"));
			topicListView.setWebsite(rs.getString("website"));
			return topicListView;
		}
	}
	
	public void add(Topic topic) {
		Long id = updateForLongKey(ADD, topic);
		topic.setId(id);
	}

	public void editStatus(Long id, Integer status, Long userId) {
		update(EDIT_STATUS, status, new Date(), id, userId);
	}

	public void edit(Topic topic) {
		update(EDIT, topic);
	}
	
	public void editPic(Long id, String picId, Long userId) {
		update(EDIT_PIC, picId, new Date(), id, userId);
	}
	
	public void editLooks(Long id, Integer looksPlus){
		this.update(EDIT_LOOKS, looksPlus, id);
	}
	
	public void editLikes(Long id, Integer likesPlus){
		this.update(EDIT_LIKES, likesPlus, id);
	}
	
	public void editReplies(Long id, Integer repliesPlus){
		this.update(EDIT_REPLIES, repliesPlus, id);
	}
	
	public Topic findById(Long id){
		return findOne(FIND_TOPIC_BY_ID, new TopicRowMapper(), id);
	}
	
	public TopicView findTopicViewById(Long id){
		return findOne(FIND_TOPICVIEW_BY_ID, new TopicViewRowMapper(), id);
	}
	
	public List<TopicListView> findTopicListView(String orderByField, int start, int offset){
		StringBuilder sql = new StringBuilder(FIND_TOPICLISTVIEW);
		sql.append(orderBy(orderByField));
		sql.append(limit());
		
		return findList(sql.toString(), new TopicListViewRowMapper(), start, offset);
	}
	public long findTopicListViewCount(){
		return findLong(FIND_TOPICLISTVIEW_COUNT);
	}
	
	public List<TopicListView> findTopicListViewByTagId(Integer tagId, String orderByField, int start, int offset){
		StringBuilder sql = new StringBuilder(FIND_TOPICLISTVIEW_BY_TAGID);
		sql.append(orderBy(orderByField));
		sql.append(limit());
		
		return findList(sql.toString(), new TopicListViewRowMapper(), tagId, start, offset);
	}
	public long findTopicListViewByTagIdCount(Integer tagId){
		return findLong(FIND_TOPICLISTVIEW_BY_TAGID_COUNT, tagId);
	}*/
	
}
